package com.hjm.common.utils.yh;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.DateUtil;
import java.io.*;
import java.text.DecimalFormat;
import java.util.*;

/**
 * 读取Excel
 *
 * @author zengwendong
 */
public class PoiUtil {
    private static final String VERSION_2003_SUFFIX = ".xls";
    private static final String VERSION_2007_SUFFIX = ".xlsx";
    private Workbook wb;
    private Sheet sheet;
    private Row row;

    /** 每个sheet页存储5w条数据，多了分sheet页 */
    private static int perSheetCount = 50000;

    /**
     * 根据后缀获取Workbook
     * @param filePath String
     * @param inputStream InputStream
     * @return Workbook
     * @throws Exception e
     */
    private static Workbook getWorkBookBySuffix(String filePath, InputStream inputStream) throws Exception{
        String ext = filePath.substring(filePath.lastIndexOf("."));
        if (VERSION_2003_SUFFIX.equals(ext)) {
            return new HSSFWorkbook(inputStream);
        } else if (VERSION_2007_SUFFIX.equals(ext)) {
            return new XSSFWorkbook(inputStream);
        }
        return null;
    }

    /**
     * 根据Cell类型设置数据
     * @param cell Cell
     * @return Object
     */
    private static Object getCellFormatValue(Cell cell) {
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                // 如果当前Cell的Type为NUMERIC
                case Cell.CELL_TYPE_NUMERIC:
                case Cell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式
                        return cell.getDateCellValue();
                    } else {
                        // 如果是纯数字
                       // DecimalFormat df = new DecimalFormat("0");
                        // 取得当前Cell的数值
                        //return df.format(cell.getNumericCellValue());
                        cell.setCellType(1);
                        return cell.getStringCellValue().trim();
                    }
                }
                // 如果当前Cell的Type为STRING
                case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString();
                // 默认的Cell值
                default: return "";
            }
        } else {
            return "";
        }
    }

    public PoiUtil(String filepath) {
        if (filepath == null) {
            return;
        }
        String ext = filepath.substring(filepath.lastIndexOf("."));
        try {
            InputStream is = new FileInputStream(filepath);
            if (VERSION_2003_SUFFIX.equals(ext)) {
                wb = new HSSFWorkbook(is);
            } else if (VERSION_2007_SUFFIX.equals(ext)) {
                wb = new XSSFWorkbook(is);
            } else {
                wb = null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 读取Excel数据内容
     *
     * @param
     * @return Map 包含单元格数据内容的Map对象
     * @author zengwendong
     */
    public Map<String, Map<String, Object>> readExcelContent() throws Exception {
        if (wb == null) {
            throw new Exception("Workbook对象为空！");
        }
        Map<String, Map<String, Object>> content = new HashMap<String, Map<String, Object>>();

        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<String, Object> cellValue = new HashMap<String, Object>();
            while (j < colNum) {
                Object obj = getCellFormatValue(row.getCell(j));
                if(null!=obj && !"".equals(obj)){
                    cellValue.put("key" + j, obj);
                }
                j++;
            }
            if(cellValue.size()>0){
                content.put("key" + i, cellValue);
            }
        }
        return content;
    }


    /**
     * 读取Excel数据内容
     *
     * @param filepath String
     * @return Map 包含单元格数据内容的Map对象
     * @author zengwendong
     */
    public static Map<String, Map<String, Object>> readExcelContent(String filepath) throws Exception {
        Workbook wb = null;
        InputStream is = null;
        try {
            is = new FileInputStream(filepath);
            wb = getWorkBookBySuffix(filepath, is);

            Map<String, Map<String, Object>> content = new LinkedHashMap<>();

            Sheet sheet = wb.getSheetAt(0);
            // 得到总行数
            int rowNum = sheet.getLastRowNum();
            Row row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                if (row == null){
                    break;
                }
                int j = 0;
                Map<String, Object> cellValue = new HashMap<String, Object>();
                while (j < colNum) {
                    if(row.getCell(0)==null||row.getCell(0).toString()==""){
                        break;
                    }
                    Object obj = getCellFormatValue(row.getCell(j));
                    if(null!=obj && !"".equals(obj)){
                        cellValue.put("key" + j, obj);
                    }
                    j++;
                }
                if(cellValue.size()>0){
                    content.put("key" + i, cellValue);
                }
            }
            return content;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }finally {
            if(is!=null){
                is.close();
            }
            if(wb!=null){
                wb.close();
            }
        }
    }


    /**
     * 读取Excel数据内容
     *
     * @param
     * @return Map 包含单元格数据内容的Map对象
     * @author zengwendong
     */
    public static Map<String, Map<String, Object>> readExcelContent(String filepath,int sheetat) throws Exception {
        Workbook wb = null;
        InputStream is=null;
        try {
            is = new FileInputStream(filepath);
            wb = getWorkBookBySuffix(filepath, is);

            Map<String, Map<String, Object>> content = new HashMap<>();
            Sheet sheet = wb.getSheetAt(sheetat);
            // 得到总行数
            int rowNum = sheet.getLastRowNum();
            Row row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 2; i <= rowNum; i++) {
                row = sheet.getRow(i);
                int j = 0;
                if(row.getCell(0)==null||row.getCell(0).toString()==""){
                    break;
                }
                Map<String, Object> cellValue = new HashMap<>();
                while (j < colNum) {
                    Object obj = getCellFormatValue(row.getCell(j));
                    cellValue.put("key" + j, obj);
                    j++;
                }
                content.put("key" + i, cellValue);
                System.out.println("========================================"+i);
            }
            return content;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if(is!=null){
                is.close();
            }
            if(wb!=null){
                wb.close();
            }
        }
    }

    public static LinkedHashMap<String, Map<String, Object>> readExcelContent1(String filepath,int sheetat) throws Exception {
        Workbook wb = null;
        InputStream is=null;
        try {
            is = new FileInputStream(filepath);
            wb = getWorkBookBySuffix(filepath, is);
            LinkedHashMap<String, Map<String, Object>> content = new LinkedHashMap<>();
            Sheet sheet = wb.getSheetAt(sheetat);
            // 得到总行数
            int rowNum = sheet.getLastRowNum();
            Row row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 2; i <= rowNum; i++) {
                row = sheet.getRow(i);
                int j = 0;
                if(row.getCell(0)==null||row.getCell(0).toString()==""){
                    break;
                }
                Map<String, Object> cellValue = new HashMap<>();
                while (j < colNum) {
                    Object obj = getCellFormatValue(row.getCell(j));
                    cellValue.put("key" + j, obj);
                    j++;
                }
                content.put("key" + i, cellValue);
                System.out.println("========================================"+i);
            }
            return content;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if(is!=null){
                is.close();
            }
            if(wb!=null){
                wb.close();
            }
        }
    }


    /**
     * @Author: gxg
     * @Date: 2019/11/18 10:38
     * @Description:  数据量大时分sheet页
     * @Version: 1.0
     */
    public static File poi(List<Object[]> list, int[] colunwidth, String fielName, String path, String type) throws IOException {
        return poi(null,list,colunwidth,fielName,path,type);
    }

    /**
     * @Author: gxg
     * @Date: 2019/11/18 10:38
     * @Description:  数据量大时分sheet页
     * @Version: 1.0
     */
    public static File poi(String[] exceltitle,List<Object[]> list, int[] colunwidth, String fielName, String path, String type) throws IOException {
        // 创建HSSFWorkbook对象(excel的文档对象)
        File file = null;
        FileOutputStream fos = null;
        Workbook wb=null;
        try {
            File isfile = new File(path);
            if (!isfile.exists()) {
                isfile.mkdirs();
            }
            file = new File(path, fielName + "." + type);
            file.createNewFile();
            fos = new FileOutputStream(file);
            if ("xlsx".equals(type)) {
                wb = new XSSFWorkbook();
                int listSize = list.size();
                for (int page = 0 ; page < listSize/perSheetCount+1 ; page ++){
                    List<Object[]> listFrom = (List<Object[]>) list.subList((page) * perSheetCount, (page + 1) * perSheetCount > listSize ? list.size() : (page + 1) * perSheetCount);
                    List<Object[]> listTo = new ArrayList();
                    for (Object[] objects : listFrom) {
                        listTo.add(objects);
                    }
                    if (null != exceltitle) {
                        listTo.add(0,exceltitle);
                    }
                    // 建立新的sheet对象（excel的表单）
                    Sheet sheet = wb.createSheet("sheet"+(page+1));
                    // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
                    // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
                    CellStyle cellStyle = wb.createCellStyle();
                    Font fontStyle = wb.createFont();
                    fontStyle.setFontName("宋体");
                    fontStyle.setFontHeightInPoints((short) 14);
                    cellStyle.setFont(fontStyle);
                    // 在sheet里创建第二行
                    for (int i = 0; i < listTo.size(); i++) {
                        Row row = sheet.createRow(i);
                        row.setRowStyle(cellStyle);
                        for (int j = 0; j < listTo.get(i).length; j++) {
                            //设置单元格值
                            row.createCell(j).setCellValue(listTo.get(i)[j] + "");
                            //设置指定列的列宽，256 * 50这种写法是因为width参数单位是单个字符的256分之一
                            if (colunwidth[j] != 0) {
                                sheet.setColumnWidth(j, 256 * colunwidth[j]);
                            }
                        }
                    }
                }
                wb.write(fos);
            } else if ("xls".equals(type)) {
                wb = new HSSFWorkbook();
                // 建立新的sheet对象（excel的表单）
                int listSize = list.size();
                for (int page = 0 ; page < listSize/perSheetCount+1 ; page ++) {
                    List<Object[]> listFrom = (List<Object[]>) list.subList((page) * perSheetCount, (page + 1) * perSheetCount > listSize ? list.size() : (page + 1) * perSheetCount);
                    List<Object[]> listTo = new ArrayList();
                    for (Object[] objects : listFrom) {
                        listTo.add(objects);
                    }
                    if (null != exceltitle) {
                        listTo.add(0,exceltitle);
                    }
                    Sheet sheet = wb.createSheet("sheet"+(page+1));
                    // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
                    // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
                    CellStyle cellStyle = wb.createCellStyle();
                    Font fontStyle = wb.createFont();
                    fontStyle.setFontName("宋体");
                    fontStyle.setFontHeightInPoints((short) 14);
                    cellStyle.setFont(fontStyle);
                    // 在sheet里创建第二行
                    for (int i = 0; i < listTo.size(); i++) {
                        Row row = sheet.createRow(i);
                        row.setRowStyle(cellStyle);
                        for (int j = 0; j < listTo.get(i).length; j++) {
                            //设置单元格值
                            row.createCell(j).setCellValue(listTo.get(i)[j] + "");
                            //设置指定列的列宽，256 * 50这种写法是因为width参数单位是单个字符的256分之一
                            if (colunwidth[j] != 0) {
                                sheet.setColumnWidth(j, 256 * colunwidth[j]);
                            }
                        }
                    }
                }
                wb.write(fos);
            }
        } catch (IOException e) {
            throw e;
        }finally {
            if(null!=list&&list.size()>0){
                list.clear();
            }
            if(null !=fos){
                fos.close();
            }
            if(null!=wb){
                wb.close();
            }
        }

        return file;
    }

    public static File poi(List<Object[]> list, int[] colunwidth, String fielName, String path, String type, String sheetName) throws IOException {
        // 创建HSSFWorkbook对象(excel的文档对象)
        File file = null;
        FileOutputStream fos = null;
        Workbook wb=null;
        try {
            file = new File(path, fielName + "." + type);
            File isfile = new File(path);
            if (!isfile.exists()) {
                isfile.mkdirs();
            }
            file.createNewFile();
            fos = new FileOutputStream(file);

            if ("xlsx".equals(type)) {
                wb = new XSSFWorkbook();
                // 建立新的sheet对象（excel的表单）
                Sheet sheet = wb.createSheet(sheetName);
                // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
                // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
                CellStyle cellStyle = wb.createCellStyle();
                Font fontStyle = wb.createFont();
                fontStyle.setFontName("宋体");
                fontStyle.setFontHeightInPoints((short) 14);
                cellStyle.setFont(fontStyle);
                // 在sheet里创建第二行
                for (int i = 0; i < list.size(); i++) {
                    Row row = sheet.createRow(i);
                    row.setRowStyle(cellStyle);
                    for (int j = 0; j < list.get(i).length; j++) {
                        //设置单元格值
                        row.createCell(j).setCellValue(list.get(i)[j] + "");
                        //设置指定列的列宽，256 * 50这种写法是因为width参数单位是单个字符的256分之一
                        if (colunwidth[j] != 0) {
                            sheet.setColumnWidth(j, 256 * colunwidth[j]);
                        }
                    }
                }

                wb.write(fos);
            } else if ("xls".equals(type)) {
                wb = new HSSFWorkbook();
                // 建立新的sheet对象（excel的表单）
                Sheet sheet = wb.createSheet(sheetName);
                // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
                // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
                CellStyle cellStyle = wb.createCellStyle();
                Font fontStyle = wb.createFont();
                fontStyle.setFontName("宋体");
                fontStyle.setFontHeightInPoints((short) 14);
                cellStyle.setFont(fontStyle);
                // 在sheet里创建第二行
                for (int i = 0; i < list.size(); i++) {
                    Row row = sheet.createRow(i);
                    row.setRowStyle(cellStyle);
                    for (int j = 0; j < list.get(i).length; j++) {
                        //设置单元格值
                        row.createCell(j).setCellValue(list.get(i)[j] + "");
                        //设置指定列的列宽，256 * 50这种写法是因为width参数单位是单个字符的256分之一
                        if (colunwidth[j] != 0) {
                            sheet.setColumnWidth(j, 256 * colunwidth[j]);
                        }
                    }
                }
                wb.write(fos);
            }
        } catch (IOException e) {
            throw e;
        }finally {
            if(null!=list&&list.size()>0){
                list.clear();
            }
            if(null !=fos){
                fos.close();
            }
            if(null!=wb){
                wb.close();
            }
        }

        return file;
    }


    /**
     * ********************************************************.<br>
     * [方法] toList <br>
     * [描述] 处理结果集 生成创建文件的lsit <br>
     * [参数] (对参数的描述) <br>
     * [返回] List<Object[]> <br>
     * [时间] 2017年10月31日 上午10:02:19 <br>
     * [作者] 刘浩龙 【LHL】
     * ********************************************************.<br>
     */
    public static List<Object[]> toList(List<Map<String, Object>> list, Map<String, Map<String, String>> param, String[] names, String[] titles) {
        List<Object[]> dataList = new ArrayList<Object[]>();
        dataList.add(titles);
        for (int i = 0; i < list.size(); i++) {
            Map<String, Object> data = list.get(i);
            String[] strs = new String[names.length];
            for (int j = 0; j < names.length; j++) {
                String key = names[j];
                if (data.get(key) != null && !"".equals(data.get(key))) {
                    if (param != null && param.get(key) != null && !"".equals(param)) {
                        strs[j] = param.get(key).get(data.get(key).toString());

                    } else {
                        strs[j] = data.get(key).toString();
                    }
                } else {
                    strs[j] = "";
                }
            }
            dataList.add(strs);
        }
        return dataList;
    }


    /**
     * <p>
     * Description:判断时间类型
     * </p>
     *
     * @param sheet    sheet
     * @param i        行
     * @param colIndex 列
     * @return val 返回的string
     */
    public static String toNumeric(XSSFSheet sheet, int i, int colIndex) {
        Cell cell = sheet.getRow(i).getCell(colIndex);
        String val = null;
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                val = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                Boolean val1 = cell.getBooleanCellValue();
                val = val1.toString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (XSSFDateUtil.isCellDateFormatted(cell)) {     //日期类型
                    val = cell.toString();
                } else {
                    DecimalFormat df = new DecimalFormat("0");
                    val = df.format(cell.getNumericCellValue());                            //数值类型的
                }
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
        }
        return val;
    }

    public static class XSSFDateUtil extends DateUtil {
        protected static int absoluteDay(Calendar cal, boolean use1904windowing) {
            return DateUtil.absoluteDay(cal, use1904windowing);
        }
    }

    /**
     * @Author: Liyh
     * @Date: 2020/6/15 14:11
     * @Description:   读取excel文件内容
     * @Version: 1.0
     */
    public static Map<String, Map<String, Object>> readShippExcelContent(String filepath) throws Exception {
        Workbook wb = null;
        InputStream is = null;
        try {
            is = new FileInputStream(filepath);
            wb = getWorkBookBySuffix(filepath, is);

            Map<String, Map<String, Object>> content = new LinkedHashMap<>();

            Sheet sheet = wb.getSheetAt(0);
            // 得到总行数
            int rowNum = sheet.getLastRowNum();
            Row row = sheet.getRow(0);
            int colNum = row.getPhysicalNumberOfCells();
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                if (row == null){
                    break;
                }
                int j = 0;
                Map<String, Object> cellValue = new HashMap<String, Object>();
                while (j < colNum) {
//                    if(row.getCell(0)==null||row.getCell(0).toString()==""){
//                        break;
//                    }
                    Object obj = getCellFormatValue(row.getCell(j));
                    if(null!=obj && !"".equals(obj)){
                        cellValue.put("key" + j, obj);
                    }
                    j++;
                }
                if(cellValue.size()>0){
                    content.put("key" + i, cellValue);
                }
            }
            return content;
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }finally {
            if(is!=null){
                is.close();
            }
            if(wb!=null){
                wb.close();
            }
        }
    }
}
